#!/usr/bin/python
#coding=utf-8

from sqlalchemy.types import VARCHAR, DECIMAL
import pandas as pd
import datetime
from common import df_operator as op,db_utils as util
import sys


furnace = """
    CREATE TABLE BGTARAS1.PREQ503_BPC_FURNACE(
        UNIT_CODE VARCHAR(4),
        PRODUCE_TIME VARCHAR(20),
        CLASS VARCHAR(5),
        SHIFT VARCHAR(5),
        PROD_COILNO VARCHAR(20),
        ENTRY_COILNO VARCHAR(20),
        FWTEMP_PVIN10_1 DECIMAL(24,6),
        FWTEMP_PVIN10_2 DECIMAL(24,6),
        FWTEMP_PVOUT10_1 DECIMAL(24,6),
        FWTEMP_PVOUT10_2 DECIMAL(24,6),
        FPRESSURE_PV3 DECIMAL(24,6),
        EX_GAS_ENTRY_TEMP DECIMAL(24,6),
        EX_GAS_EXIT_TEMP DECIMAL(24,6),
        BDOOR_SEAL_TEMP DECIMAL(24,6),
        FUEL_GAS_TEMP DECIMAL(24,6),
        CDOOR_SEAL_TEMP DECIMAL(24,6),
        FPRESSURE_SV1 DECIMAL(24,6),
        PURE_WATER_FLOW DECIMAL(24,6),
        CIRC_WATER_FLOW DECIMAL(24,6),
        INDUSTRY_WATER_FLOW DECIMAL(24,6)
    )NOT LOGGED INITIALLY
    IN "BGTARAS1"
"""
Consumption = """
    CREATE TABLE BGTARAS1.PREQ503_BPC_CONSUMPTION(
        UNIT_CODE VARCHAR(4),
        PRODUCE_TIME VARCHAR(20),
        CLASS VARCHAR(5),
        SHIFT VARCHAR(5),
        PROD_COILNO VARCHAR(20),
        ENTRY_COILNO VARCHAR(20),
        GAS_FLOW DECIMAL(24,6),
        N2_FLOW DECIMAL(24,6),
        H2_FLOW DECIMAL(24,6)
    )NOT LOGGED INITIALLY
    IN "BGTARAS1"
"""
bpcZONE = """
    CREATE TABLE BGTARAS1.PREQ503_BPC_ZONE(
        UNIT_CODE VARCHAR(4),
        PRODUCE_TIME VARCHAR(20),
        CLASS VARCHAR(5),
        SHIFT VARCHAR(5),
        PROD_COILNO VARCHAR(20),
        ENTRY_COILNO VARCHAR(20),
        ZONE_NO DECIMAL(2,0),
        BTEMP_PV DECIMAL(24,6),
        FLOW_PV DECIMAL(24,6),
        HRATIO_PV DECIMAL(24,6),
        PRESS_PV DECIMAL(24,6),
        FTEMP_PC DECIMAL(24,6),
        FTEMP_PV DECIMAL(24,6)
    )NOT LOGGED INITIALLY
    IN "BGTARAS1"
"""


def createBPC_FURNACE(start_time,end_time):
    sql = """SELECT * FROM SYSCAT.TABLES WHERE TABNAME='PREQ503_BPC_FURNACE' AND TABSCHEMA='BGTARAS1'"""
    sql2 = """SELECT * FROM SYSCAT.TABLES WHERE TABNAME='PREQ503_BPC_CONSUMPTION' AND TABSCHEMA='BGTARAS1'"""
    sql3 = """SELECT * FROM SYSCAT.TABLES WHERE TABNAME='PREQ503_BPC_ZONE' AND TABSCHEMA='BGTARAS1'"""
    sql_result = util.query(conn_rds, sql=sql)
    sql_result2 = util.query(conn_rds, sql=sql2)
    sql_result3 = util.query(conn_rds, sql=sql3)
    if sql_result.__len__() > 0:
        conn_rds.execute("drop table BGTARAS1.PREQ503_BPC_FURNACE")
    else:
        conn_rds.execute(furnace)
    if sql_result2.__len__() > 0:
        conn_rds.execute("drop table BGTARAS1.PREQ503_BPC_CONSUMPTION")
    else:
        conn_rds.execute(Consumption)
    if sql_result3.__len__() > 0:
        conn_rds.execute("drop table BGTARAS1.PREQ503_BPC_ZONE")
    else:
        conn_rds.execute(bpcZONE)

    sql = """SELECT
            A.UNIT_CODE,
            A.CLASS,
            A.SHIFT,
            A.PRODUCE_TIME,
            A.PROD_COILNO,
            A.ENTRY_COILNO,
            ROUND(AVG(CAST(C.FWTEMP_PVIN10_1 AS DOUBLE)),6)     AS FWTEMP_PVIN10_1,
            ROUND(AVG(CAST(C.FWTEMP_PVIN10_2 AS DOUBLE)),6)     AS FWTEMP_PVIN10_2,
            ROUND(AVG(CAST(C.FWTEMP_PVOUT10_1 AS DOUBLE)),6)    AS FWTEMP_PVOUT10_1,
            ROUND(AVG(CAST(C.FWTEMP_PVOUT10_2 AS DOUBLE)),6)    AS FWTEMP_PVOUT10_2,
            ROUND(AVG(CAST(C.FPRESSURE_PV3 AS DOUBLE)),6)       AS FPRESSURE_PV3,
            ROUND(AVG(CAST(C.EX_GAS_ENTRY_TEMP AS DOUBLE)),6)   AS EX_GAS_ENTRY_TEMP,
            ROUND(AVG(CAST(C.EX_GAS_EXIT_TEMP AS DOUBLE)),6)    AS EX_GAS_EXIT_TEMP,
            ROUND(AVG(CAST(C.BDOOR_SEAL_TEMP AS DOUBLE)),6)     AS BDOOR_SEAL_TEMP,
            ROUND(AVG(CAST(C.FUEL_GAS_TEMP AS DOUBLE)),6)       AS FUEL_GAS_TEMP,
            ROUND(AVG(CAST(C.CDOOR_SEAL_TEMP AS DOUBLE)),6)     AS CDOOR_SEAL_TEMP,
            ROUND(AVG(CAST(C.FPRESSURE_SV1 AS DOUBLE)),6)       AS FPRESSURE_SV1,
            ROUND(AVG(CAST(C.PURE_WATER_FLOW AS DOUBLE)),6)     AS PURE_WATER_FLOW,
            ROUND(AVG(CAST(C.CIRC_WATER_FLOW AS DOUBLE)),6)     AS CIRC_WATER_FLOW,
            ROUND(AVG(CAST(C.INDUSTRY_WATER_FLOW AS DOUBLE)),6) AS INDUSTRY_WATER_FLOW
        FROM
            BGTARAS1.T_ADS_FACT_SICB_CD0201 A
        LEFT JOIN
            BGTAROQ503.T_ODS_BPC_PROD_DATA B
        ON
            B.COIL_ID = A.PROD_COILNO
        LEFT JOIN
            BGTAROQ503.T_ODS_BPC_FURNACE C
        ON
            C.DATE_TIME BETWEEN B.PROD_START AND B.PROD_END
        INNER JOIN
            BGTAROQ503.T_ODS_ROTATE_START_FLAG D
        ON
            C.date_time>=D.TIME_FROM
        AND C.date_time<=D.TIME_TO
        WHERE
            A.UNIT_CODE='Q503'
        AND A.PRODUCE_TIME BETWEEN """+start_time+""" AND """+end_time+"""
        GROUP BY
            A.UNIT_CODE,
            A.CLASS,
            A.SHIFT,
            A.PRODUCE_TIME,
            A.PROD_COILNO,
            A.ENTRY_COILNO
        """
    df_result = util.query(conn_rds, sql=sql)
    util.insert(df_result, conn_rds, "PREQ503_BPC_FURNACE", "BGTARAS1")

    sql2 = """SELECT
        A.UNIT_CODE,
        A.CLASS,
        A.SHIFT,
        A.PRODUCE_TIME,
        A.PROD_COILNO,
        A.ENTRY_COILNO,
        ROUND(AVG(C.GAS_FLOW),6) AS GAS_FLOW,
        ROUND(AVG(C.N2_FLOW),6)  AS N2_FLOW,
        ROUND(AVG(C.H2_FLOW),6)  AS H2_FLOW
    FROM
        BGTARAS1.T_ADS_FACT_SICB_CD0201 A
    LEFT JOIN
        BGTAROQ503.T_ODS_BPC_PROD_DATA B
    ON
        B.COIL_ID = A.PROD_COILNO
    LEFT JOIN
        BGTAROQ503.T_ODS_BPC_CONSUMPTION C
    ON
        C.DATE_TIME BETWEEN B.PROD_START AND B.PROD_END
    INNER JOIN
        BGTAROQ503.T_ODS_ROTATE_START_FLAG D
    ON
        C.date_time>=D.TIME_FROM
    AND C.date_time<=D.TIME_TO
    WHERE
        A.UNIT_CODE='Q503'
    AND A.PRODUCE_TIME BETWEEN """ + start_time + """ AND """ + end_time + """
    GROUP BY
        A.UNIT_CODE,
        A.CLASS,
        A.SHIFT,
        A.PRODUCE_TIME,
        A.PROD_COILNO,
        A.ENTRY_COILNO
        """
    df_result2 = util.query(conn_rds, sql=sql2)
    util.insert(df_result2, conn_rds, "PREQ503_BPC_CONSUMPTION", "BGTARAS1")

    sql3 = """SELECT
    A.UNIT_CODE,
    A.CLASS,
    A.SHIFT,
    A.PRODUCE_TIME,
    A.PROD_COILNO,
    A.ENTRY_COILNO,
    C.ZONE_NO,
    CASE
        WHEN B.POSITION=0
        THEN AVG(CAST(C.BTEMP_PVIN AS DOUBLE))
        ELSE AVG(CAST(C.BTEMP_PVOUT AS DOUBLE))
    END                                          AS BTEMP_PV,--底盘温度
    AVG(CAST((C.HFLOW_PV+C.NFLOW_PV) AS DOUBLE)) AS FLOW_PV,--保护气体流量
    AVG(CAST(C.HRATIO_PV AS DOUBLE))             AS HRATIO_PV,--h2比率
    CASE
        WHEN B.POSITION=0
        THEN AVG(CAST(C.PRESS_PVIN AS DOUBLE))
        ELSE AVG(CAST(C.PRESS_PVOUT AS DOUBLE))
    END                             AS PRESS_PV,--内罩压力
    AVG(CAST(C.FTEMP_PC AS DOUBLE)) AS FTEMP_PC,--L2炉温计算值
    AVG(
        CASE C.FTEMP_CM
            WHEN 1
            THEN C.FTEMP_PVIN
            WHEN 2
            THEN C.FTEMP_PVOUT
            WHEN 3
            THEN CAST(C.FTEMP_PVIN+C.FTEMP_PVOUT AS DOUBLE)/2
        END) AS FTEMP_PV--炉温
FROM
    BGTARAS1.T_ADS_FACT_SICB_CD0201 A
LEFT JOIN
    BGTAROQ503.T_ODS_BPC_COILS_PRIM B
ON
    B.COIL_ID=A.PROD_COILNO
LEFT JOIN
    BGTAROQ503.T_ODS_BPC_ZONE C
ON
    C.COIL_ID_IN = A.PROD_COILNO
OR  C.COIL_ID_OUT = A.PROD_COILNO
INNER JOIN
    BGTAROQ503.T_ODS_ROTATE_START_FLAG D
ON
    C.date_time>=D.TIME_FROM
AND C.date_time<=D.TIME_TO
WHERE
    A.UNIT_CODE='Q503'
AND A.PRODUCE_TIME BETWEEN """+start_time+""" AND """+end_time+"""
GROUP BY
    A.UNIT_CODE,
    A.CLASS,
    A.SHIFT,
    A.PRODUCE_TIME,
    A.PROD_COILNO,
    A.ENTRY_COILNO,
    B.POSITION,
    C.ZONE_NO
        """
    df_result3 = util.query(conn_rds, sql=sql3)
    util.insert(df_result3, conn_rds, "PREQ503_BPC_ZONE", "BGTARAS1")




def PreQ503(start_time, end_time):
    global conn_rds
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    print(datetime.datetime.now())
    createBPC_FURNACE(start_time, end_time)
    print(datetime.datetime.now())
    conn_rds.close()

def main():
    PreQ503('20211021150108','20211022150108')

if __name__ == '__main__':
    main()